"""
SQL Server Manager 基础操作示例

该示例演示了如何使用 SQLServerManager 类进行数据库连接、数据插入、查询、更新和删除等操作。
"""
from modules.database.sqlserver_manager import SQLServerManager
from modules.logger import LogManager
import time
import csv

# 配置日志
logger = LogManager.get_console_logger(name='sqlserver_example')

# 数据库连接参数
DB_CONFIG = {
    'server': 'localhost',
    'database': 'TestDB',
    'username': 'sa',
    'password': 'YourStrong!Passw0rd',
    'port': 1433,
    'driver': '{ODBC Driver 17 for SQL Server}'
}


def create_demo_table(db_manager: SQLServerManager) -> bool:
    """创建演示表"""
    # 创建测试表
    create_table_sql = """
    CREATE TABLE IF NOT EXISTS Employees (
        ID INT IDENTITY(1,1) PRIMARY KEY,
        Name NVARCHAR(50) NOT NULL,
        Age INT,
        Department NVARCHAR(50),
        Salary DECIMAL(10,2),
        HireDate DATETIME,
        IsActive BIT DEFAULT 1
    )
    """
    
    result = db_manager.execute_non_query(create_table_sql)
    return result != -1


def main():
    # 创建数据库管理器实例
    db_manager = SQLServerManager(**DB_CONFIG)
    
    try:
        # 连接数据库
        if not db_manager.connect():
            logger.error("无法连接到数据库，程序退出")
            return

        # 创建演示表
        if not create_demo_table(db_manager):
            logger.error("创建演示表失败")
            return

        # 1. 插入单行数据
        new_employee = {
            'Name': '张三',
            'Age': 30,
            'Department': '技术部',
            'Salary': 8000.00,
            'HireDate': '2023-01-15',
            'IsActive': True
        }
        insert_result = db_manager.insert('Employees', new_employee)
        if insert_result > 0:
            logger.info(f"成功插入 {insert_result} 条记录")
        
        # 2. 批量插入数据
        batch_employees = [
            {
                'Name': '李四',
                'Age': 28,
                'Department': '市场部',
                'Salary': 7500.00,
                'HireDate': '2023-03-20',
                'IsActive': True
            },
            {
                'Name': '王五',
                'Age': 35,
                'Department': '财务部',
                'Salary': 9000.00,
                'HireDate': '2022-11-10',
                'IsActive': True
            }
        ]
        batch_result = db_manager.batch_insert('Employees', batch_employees)
        if batch_result > 0:
            logger.info(f"成功批量插入 {batch_result} 条记录")
        
        # 3. 查询数据
        query_sql = "SELECT * FROM Employees WHERE Department = ?"
        employees = db_manager.execute_query(query_sql, ('技术部',))
        logger.info(f"查询到 {len(employees)} 条技术部员工记录:")
        for emp in employees:
            logger.info(f"ID: {emp['ID']}, 姓名: {emp['Name']}, 年龄: {emp['Age']}, 薪资: {emp['Salary']}")
        
        # 4. 更新数据
        update_data = {'Salary': 8500.00}
        update_result = db_manager.update(
            'Employees',
            update_data,
            'Name = ?',
            ('张三',)
        )
        if update_result > 0:
            logger.info(f"成功更新 {update_result} 条记录")
        
        # 5. 删除数据 (演示用，实际操作需谨慎)
        delete_result = db_manager.delete('Employees', 'Name = ?', ('李四',))
        if delete_result > 0:
            logger.info(f"成功删除 {delete_result} 条记录")
        
        # 6. 执行存储过程示例 (SQL Server 2016+ 支持 CREATE OR ALTER)
        # 首先创建演示存储过程
        create_proc_sql = """
        CREATE OR ALTER PROCEDURE GetDepartmentEmployees
            @Department NVARCHAR(50)
        AS
        BEGIN
            SET NOCOUNT ON; -- 防止额外的影响行数消息
            SELECT * FROM Employees WHERE Department = @Department
        END
        """
        db_manager.execute_non_query(create_proc_sql)
        
        # 执行存储过程
        proc_results = db_manager.execute_stored_procedure('GetDepartmentEmployees', ('技术部',))
        if proc_results:
            logger.info(f"存储过程返回 {len(proc_results[0])} 条记录")
            for emp in proc_results[0]:
                logger.info(f"存储过程结果 - ID: {emp['ID']}, 姓名: {emp['Name']}")
        
        # 7. 事务处理示例
        try:
            db_manager.connection.autocommit = False  # 开始事务
            
            # 插入两条记录作为事务操作
            tx_employees = [
                {
                    'Name': '赵六',
                    'Age': 40,
                    'Department': '管理层',
                    'Salary': 15000.00,
                    'HireDate': '2021-05-01',
                    'IsActive': True
                },
                {
                    'Name': '孙七',
                    'Age': 33,
                    'Department': '技术部',
                    'Salary': 9500.00,
                    'HireDate': '2022-03-18',
                    'IsActive': True
                }
            ]
            
            tx_result = db_manager.batch_insert('Employees', tx_employees)
            logger.info(f"事务中批量插入 {tx_result} 条记录")
            
            # 提交事务
            db_manager.connection.commit()
            logger.info("事务提交成功")
        except Exception as e:
            # 回滚事务
            db_manager.connection.rollback()
            logger.error(f"事务执行失败，已回滚: {str(e)}")
        finally:
            db_manager.connection.autocommit = True
        
        # 8. 批量更新示例
        batch_update_data = [
            ({'Salary': 9000.00}, 'Name = ?', ('张三',)),
            ({'Salary': 10000.00}, 'Name = ?', ('孙七',))
        ]
        
        total_updated = 0
        for update_data, where_clause, params in batch_update_data:
            result = db_manager.update('Employees', update_data, where_clause, params)
            if result > 0:
                total_updated += result
        
        logger.info(f"批量更新完成，共更新 {total_updated} 条记录")
        
        # 9. 数据导出示例
        all_employees = db_manager.execute_query("SELECT * FROM Employees")
        if all_employees:
            # 导出到CSV
            with open('employees_export.csv', 'w', encoding='utf-8', newline='') as f:
                writer = csv.DictWriter(f, fieldnames=all_employees[0].keys())
                writer.writeheader()
                writer.writerows(all_employees)
            logger.info(f"成功将 {len(all_employees)} 条记录导出到 employees_export.csv")
        
        # 10. 处理大数据集示例 (使用分页查询)
        page_size = 2
        page_number = 1
        offset = (page_number - 1) * page_size
        
        paginated_query = """
        SELECT * FROM Employees
        ORDER BY HireDate DESC
        OFFSET ? ROWS FETCH NEXT ? ROWS ONLY
        """
        
        paginated_results = db_manager.execute_query(paginated_query, (offset, page_size))
        logger.info(f"分页查询 (第 {page_number} 页，每页 {page_size} 条):")
        for emp in paginated_results:
            logger.info(f"ID: {emp['ID']}, 姓名: {emp['Name']}, 入职日期: {emp['HireDate']}")
        
        # 11. 上下文管理器使用示例
        logger.info("\n=== 使用上下文管理器进行数据库操作 ===")
        with SQLServerManager(**DB_CONFIG) as context_db:
            if context_db.connection:
                ctx_results = context_db.execute_query("SELECT TOP 1 Name, Department FROM Employees")
                if ctx_results:
                    logger.info(f"上下文管理器查询结果: {ctx_results[0]['Name']} - {ctx_results[0]['Department']}")
        
        # 清理演示数据 (实际生产环境中谨慎使用)
        logger.info("\n=== 清理演示数据 ===")
        # 删除存储过程
        db_manager.execute_non_query("DROP PROCEDURE IF EXISTS GetDepartmentEmployees")
        # 删除演示表
        db_manager.execute_non_query("DROP TABLE IF EXISTS Employees")
        logger.info("演示数据清理完成")
        
        logger.info("\n=== 所有数据库操作示例执行完成 ===")
    except Exception as e:
        logger.error(f"数据库操作失败: {e}")
        logger.error("请检查数据库连接配置")